//DevNETCoder
//CopyRight By DevNET Group
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using STT.Entities;
using STT.DataAccess;
 
namespace STT.Business
{
    public class BTbHocVien
    {
//-----------------------------------------------------------//
        public static DataTable SelectAll()
        {
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectAll", null);
            return all;
        }
        public static DataTable SelectTop(int Top)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"Top", Top);
            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "TbHocVien_SelectTop", pr);
            return all;
        }
//-----------------------------------------------------------//
        public static void Insert(ETbHocVien OTbHocVien)
        {
            SqlParameter[] pr = new SqlParameter[17];
            pr[0] = new SqlParameter(@"MaHV", OTbHocVien.MaHV);
            pr[1] = new SqlParameter(@"TenHV", OTbHocVien.TenHV);
            pr[2] = new SqlParameter(@"Tuoi", OTbHocVien.Tuoi);
            pr[3] = new SqlParameter(@"QueQuan", OTbHocVien.QueQuan);
            pr[4] = new SqlParameter(@"DiaChi", OTbHocVien.DiaChi);
            pr[5] = new SqlParameter(@"ThoiGianCT", OTbHocVien.ThoiGianCT);
            pr[6] = new SqlParameter(@"Sdt", OTbHocVien.Sdt);
            pr[7] = new SqlParameter(@"SoCMT", OTbHocVien.SoCMT);
            pr[8] = new SqlParameter(@"Anh", OTbHocVien.Anh);
            pr[9] = new SqlParameter(@"MaLop", OTbHocVien.MaLop);
            pr[10] = new SqlParameter(@"MaDonVi", OTbHocVien.MaDonVi);
            pr[11] = new SqlParameter(@"MaNganh", OTbHocVien.MaNganh);
            pr[12] = new SqlParameter(@"MaChucVu", OTbHocVien.MaChucVu);
            pr[13] = new SqlParameter(@"MaPB", OTbHocVien.MaPB);
            pr[14] = new SqlParameter(@"MaTDChuyenMon", OTbHocVien.MaTDChuyenMon);
            pr[15] = new SqlParameter(@"MaTDHocVan", OTbHocVien.MaTDHocVan);
            pr[16] = new SqlParameter(@"GhiChu", OTbHocVien.GhiChu);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"TbHocVien_Insert", pr);
        }
        public static DataTable StatisticsCoursesBySearch(ETbKhoaHoc KHoaHoc, string TenHocVien, int Number, string Class, string NamHoc)
        {
            SqlParameter[] pr = new SqlParameter[9];
            pr[0] = new SqlParameter(@"IdCourses", KHoaHoc.MaKhoaHoc == -1 ? (object)DBNull.Value : KHoaHoc.MaKhoaHoc.ToString());
            pr[1] = new SqlParameter(@"NameCourses", KHoaHoc.TenKhoaHoc == "" ? (object)DBNull.Value : KHoaHoc.TenKhoaHoc);
            pr[2] = new SqlParameter(@"NameTeachers", TenHocVien == "" ? (object)DBNull.Value : TenHocVien);
            pr[3] = new SqlParameter(@"NumberStudent", Number == -1 ? (object)DBNull.Value : Number.ToString());
            pr[4] = new SqlParameter(@"AddressLearn", KHoaHoc.DiaDiemHoc == "" ? (object)DBNull.Value : KHoaHoc.DiaDiemHoc.ToString());
            pr[5] = new SqlParameter(@"NgayBatDau", KHoaHoc.ThoiGianBatDau == default(DateTime) ? (object)DBNull.Value : KHoaHoc.ThoiGianBatDau.ToString("dd/MM/yyyy"));
            pr[6] = new SqlParameter(@"NgayKetThuc", KHoaHoc.ThoiGianKetThuc == default(DateTime) ? (object)DBNull.Value : KHoaHoc.ThoiGianKetThuc.ToString("dd/MM/yyyy"));
            pr[7] = new SqlParameter(@"Class", Class == "" ? (object)DBNull.Value : Class);
            pr[8] = new SqlParameter(@"NamHoc", NamHoc == "" ? (object)DBNull.Value : NamHoc);

            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "StatisticsCoursesBySearch", pr);
            return all;
        }
//-----------------------------------------------------------//
        public static void Update(ETbHocVien OTbHocVien)
        {
            SqlParameter[] pr = new SqlParameter[17];
            pr[0] = new SqlParameter(@"MaHV", OTbHocVien.MaHV);
            pr[1] = new SqlParameter(@"TenHV", OTbHocVien.TenHV);
            pr[2] = new SqlParameter(@"Tuoi", OTbHocVien.Tuoi);
            pr[3] = new SqlParameter(@"QueQuan", OTbHocVien.QueQuan);
            pr[4] = new SqlParameter(@"DiaChi", OTbHocVien.DiaChi);
            pr[5] = new SqlParameter(@"ThoiGianCT", OTbHocVien.ThoiGianCT);
            pr[6] = new SqlParameter(@"Sdt", OTbHocVien.Sdt);
            pr[7] = new SqlParameter(@"SoCMT", OTbHocVien.SoCMT);
            pr[8] = new SqlParameter(@"Anh", OTbHocVien.Anh);
            pr[9] = new SqlParameter(@"MaLop", OTbHocVien.MaLop);
            pr[10] = new SqlParameter(@"MaDonVi", OTbHocVien.MaDonVi);
            pr[11] = new SqlParameter(@"MaNganh", OTbHocVien.MaNganh);
            pr[12] = new SqlParameter(@"MaChucVu", OTbHocVien.MaChucVu);
            pr[13] = new SqlParameter(@"MaPB", OTbHocVien.MaPB);
            pr[14] = new SqlParameter(@"MaTDChuyenMon", OTbHocVien.MaTDChuyenMon);
            pr[15] = new SqlParameter(@"MaTDHocVan", OTbHocVien.MaTDHocVan);
            pr[16] = new SqlParameter(@"GhiChu", OTbHocVien.GhiChu);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"TbHocVien_Update", pr);
        }
        public static void UpdateInfoHV(ETbHocVien OTbHocVien)
        {
            SqlParameter[] pr = new SqlParameter[8];
            pr[0] = new SqlParameter(@"MaHV", OTbHocVien.MaHV);
            pr[1] = new SqlParameter(@"TenHV", OTbHocVien.TenHV);
            pr[2] = new SqlParameter(@"Tuoi", OTbHocVien.Tuoi);
            pr[3] = new SqlParameter(@"QueQuan", OTbHocVien.QueQuan);
            pr[4] = new SqlParameter(@"DiaChi", OTbHocVien.DiaChi);
            pr[5] = new SqlParameter(@"Sdt", OTbHocVien.Sdt);
            pr[6] = new SqlParameter(@"Anh", OTbHocVien.Anh);
            pr[7] = new SqlParameter(@"GhiChu", OTbHocVien.GhiChu);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "HocVienUpdate2", pr);
        }
//-----------------------------------------------------------//
        public static void Delete(string MaHV)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaHV",MaHV);
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "TbHocVien_Delete", pr);
        }
//-----------------------------------------------------------//
        public static ETbHocVien SelectByID(string MaHV)
        {
            ETbHocVien OTbHocVien = new ETbHocVien();
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"MaHV",MaHV);
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure,"TbHocVien_SelectByID", pr);
            if (idr.Read())
              OTbHocVien = GetOneTbHocVien(idr);
            idr.Close();
            idr.Dispose();
            return OTbHocVien;
        }


        public static DataTable SelectStudentsBySearch(ETbHocVien HocVien, int IdCourses, int Number, int IdLanguage, int IdClass)
        {
            SqlParameter[] pr = new SqlParameter[10];
            pr[0] = new SqlParameter(@"Id", HocVien.MaHV == "-1" ? (object)DBNull.Value : HocVien.MaHV.ToString());
            pr[1] = new SqlParameter(@"Name", HocVien.TenHV == "" ? (object)DBNull.Value : HocVien.TenHV);
            pr[2] = new SqlParameter(@"IdRegency", HocVien.MaChucVu == -1 ? (object)DBNull.Value : HocVien.MaChucVu.ToString());
            pr[3] = new SqlParameter(@"IdUnit", HocVien.MaDonVi == -1 ? (object)DBNull.Value : HocVien.MaDonVi.ToString());
            pr[4] = new SqlParameter(@"IdQualification", HocVien.MaTDChuyenMon == -1 ? (object)DBNull.Value : HocVien.MaTDChuyenMon.ToString());
            pr[5] = new SqlParameter(@"Literacy", HocVien.MaTDHocVan == -1 ? (object)DBNull.Value : HocVien.MaTDHocVan.ToString());
            pr[6] = new SqlParameter(@"Number", Number == -1 ? (object)DBNull.Value : Number.ToString());
            pr[7] = new SqlParameter(@"IdCourses", IdCourses == -1 ? (object)DBNull.Value : IdCourses.ToString());
            pr[8] = new SqlParameter(@"IdLanguage", IdLanguage == -1 ? (object)DBNull.Value : IdLanguage.ToString());
            pr[9] = new SqlParameter(@"IdClass", IdClass == -1 ? (object)DBNull.Value : IdClass.ToString());

            DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "SelectStudentBySearch", pr);
            return all;
        }

//-----------------------------------------------------------//
        private static ETbHocVien GetOneTbHocVien(IDataReader idr)
       {
            ETbHocVien OTbHocVien = new ETbHocVien();
                if (idr["MaHV"] != DBNull.Value)
                    OTbHocVien.MaHV = (string)idr["MaHV"];
                if (idr["TenHV"] != DBNull.Value)
                    OTbHocVien.TenHV = (string)idr["TenHV"];
                if (idr["Tuoi"] != DBNull.Value)
                    OTbHocVien.Tuoi = (int)idr["Tuoi"];
                if (idr["QueQuan"] != DBNull.Value)
                    OTbHocVien.QueQuan = (string)idr["QueQuan"];
                if (idr["DiaChi"] != DBNull.Value)
                    OTbHocVien.DiaChi = (string)idr["DiaChi"];
                if (idr["ThoiGianCT"] != DBNull.Value)
                    OTbHocVien.ThoiGianCT = (int)idr["ThoiGianCT"];
                if (idr["Sdt"] != DBNull.Value)
                    OTbHocVien.Sdt = (string)idr["Sdt"];
                if (idr["SoCMT"] != DBNull.Value)
                    OTbHocVien.SoCMT = (string)idr["SoCMT"];
                if (idr["Anh"] != DBNull.Value)
                    OTbHocVien.Anh = (string)idr["Anh"];
                if (idr["MaLop"] != DBNull.Value)
                    OTbHocVien.MaLop = (int)idr["MaLop"];
                if (idr["MaDonVi"] != DBNull.Value)
                    OTbHocVien.MaDonVi = (int)idr["MaDonVi"];
                if (idr["MaNganh"] != DBNull.Value)
                    OTbHocVien.MaNganh = (int)idr["MaNganh"];
                if (idr["MaChucVu"] != DBNull.Value)
                    OTbHocVien.MaChucVu = (int)idr["MaChucVu"];
                if (idr["MaPB"] != DBNull.Value)
                    OTbHocVien.MaPB = (int)idr["MaPB"];
                if (idr["MaTDChuyenMon"] != DBNull.Value)
                    OTbHocVien.MaTDChuyenMon = (int)idr["MaTDChuyenMon"];
                if (idr["MaTDHocVan"] != DBNull.Value)
                    OTbHocVien.MaTDHocVan = (int)idr["MaTDHocVan"];
                if (idr["GhiChu"] != DBNull.Value)
                    OTbHocVien.GhiChu = (string)idr["GhiChu"];
            return OTbHocVien;
       }
//-----------------------------------------------------------//
        public static bool TestByID(string MaHV)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter(@"MaHV",MaHV);
            pr[1] = new SqlParameter(@"TestID",SqlDbType.Bit);
            pr[1].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"TbHocVien_TestByID", pr);
            return Convert.ToBoolean(pr[1].Value);
        }
//-----------------------------------------------------------//
        public static List<ETbHocVien> ListAll()
        {
            List<ETbHocVien> list = new List<ETbHocVien>();
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbHocVien_SelectAll", null);
            while (idr.Read())
              list.Add(GetOneTbHocVien(idr));
            if (idr.IsClosed == false)
            {
               idr.Close();
               idr.Dispose();
            }
            return list;
        }

        public static List<ETbHocVien> ListTop(int Top)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter(@"Top", Top);
            List<ETbHocVien> list = new List<ETbHocVien>();
            IDataReader idr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "TbHocVien_SelectAll", pr);
            while (idr.Read())
              list.Add(GetOneTbHocVien(idr));
            if (idr.IsClosed == false)
            {
               idr.Close();
               idr.Dispose();
            }
            return list;
        }
    //    public static DataTable SelectStudentsBySearch(ETbHocVien OTbHocVien)
    //    {
    ////            @Id nchar(15), @Name nvarchar(50),
    ////@Age int, @Address nvarchar(500), 
    ////@HomeTown nvarchar(300),@WorkingTime int,
    ////@IdRegency int, @IdUnit int,
    ////@IdQualification int, @Number int, @idLanguage int
    //        SqlParameter[] pr = new SqlParameter[17];
    //        pr[0] = new SqlParameter(@"Id", OTbHocVien.MaHV);
    //        pr[1] = new SqlParameter(@"Name", OTbHocVien.TenHV);
    //        pr[2] = new SqlParameter(@"Age", OTbHocVien.Tuoi);
    //        pr[3] = new SqlParameter(@"QueQuan", OTbHocVien.QueQuan);
    //        pr[4] = new SqlParameter(@"Address", OTbHocVien.DiaChi);
    //        pr[5] = new SqlParameter(@"WorkingTime", OTbHocVien.ThoiGianCT);
    //        pr[6] = new SqlParameter(@"Sdt", OTbHocVien.Sdt);
    //        pr[7] = new SqlParameter(@"SoCMT", OTbHocVien.SoCMT);
    //        pr[9] = new SqlParameter(@"MaLop", OTbHocVien.MaLop);
    //        pr[10] = new SqlParameter(@"MaDonVi", OTbHocVien.MaDonVi);
    //        pr[11] = new SqlParameter(@"MaNganh", OTbHocVien.MaNganh);
    //        pr[12] = new SqlParameter(@"MaChucVu", OTbHocVien.MaChucVu);
    //        pr[13] = new SqlParameter(@"MaPB", OTbHocVien.MaPB);
    //        pr[14] = new SqlParameter(@"MaTDChuyenMon", OTbHocVien.MaTDChuyenMon);
    //        pr[15] = new SqlParameter(@"MaTDHocVan", OTbHocVien.MaTDHocVan);
    //        pr[14] = new SqlParameter(@"Number", OTbHocVien.MaTDChuyenMon);            

    //        DataTable all = SqlHelper.ExecuteData(CommandType.StoredProcedure, "SelectStudentsBySearch", null);
    //        return all;
    //    }
//-----------------------------------------------------------//
      
//-----------------------------------------------------------//

    }
 
}